/*
 * hkh-project
 * leafteamwork.blogspot.com
 * Tool: Netbeans 6.7.1 - SQL Server 2005
 */
package vn.com.hkh.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.logging.Level;
import java.util.logging.Logger;
import vn.com.hkh.dto.RoomDTO;
import vn.com.hkh.util.DatabaseUtil;

/**
 *
 * @author leaf
 */
public class RoomDAO {

    // Log
    private static Logger logger = Logger.getLogger(RoomDAO.class.getName());
    //C: Create
    private final String SQL_CREATE = "INSERT INTO hkh_room_table(room_id, room_name, username, room_content, room_image, room_price, room_alias, room_when, room_position, room_status) VALUES (?,?,?,?,?,?,?,?,?,?)";
    //R: Read
    private final String SQL_READ_BY_ID = "SELECT room_id, room_name, username, room_content, room_image, room_price, room_alias, CONVERT(varchar,room_when,103) as room_when, room_position, room_status FROM hkh_room_table WHERE room_id = ?";
    private final String SQL_READ_BY_ALL = "SELECT room_id, room_name, username, room_content, room_image, room_price, room_alias, CONVERT(varchar,room_when,103) as room_when, room_position, room_status FROM hkh_room_table ORDER BY room_when DESC";
    private final String SQL_READ_BY_STATUS = "SELECT room_id, room_name, username, room_content, room_image, room_price, room_alias, CONVERT(varchar,room_when,103) as room_when, room_position, room_status FROM hkh_room_table WHERE room_status = ? ORDER BY room_position";
    private final String SQL_READ_BY_ALIAS = "SELECT room_id, room_name, username, room_content, room_image, room_price, room_alias, CONVERT(varchar,room_when,103) as room_when, room_position, room_status FROM hkh_room_table WHERE room_alias like ? ORDER BY room_position";
    //U: Update
    private final String SQL_UPDATE = "UPDATE hkh_room_table SET room_name = ?, username = ?, room_content = ?, room_image = ?, room_price = ?, room_alias = ?, room_when = ?, room_position = ?, room_status = ? WHERE room_id = ?";
    private final String SQL_UPDATE_STATUS = "UPDATE hkh_room_table SET room_status =? WHERE room_id = ?";
    //D: Delete
    private final String SQL_DELETE_BY_ID = "DELETE FROM hkh_room_table WHERE room_id = ?";

    /**
     * Create new Room
     * @param _roomDTO
     * @return RoomDTO
     */
    public RoomDTO create(RoomDTO _roomDTO) {
        Connection _con = null;
        PreparedStatement _ptmt = null;
        try {
            _con = DatabaseUtil.getConnection();
            _ptmt = _con.prepareStatement(SQL_CREATE);

            _ptmt.setString(1, _roomDTO.getId());
            _ptmt.setString(2, _roomDTO.getName());
            _ptmt.setString(3, _roomDTO.getUser().getUsername());
            _ptmt.setString(4, _roomDTO.getContent());
            _ptmt.setString(5, _roomDTO.getImage());
            _ptmt.setString(6, _roomDTO.getPrice());
            _ptmt.setString(7, _roomDTO.getAlias());
            _ptmt.setString(8, _roomDTO.getWhen());
            _ptmt.setInt(9, _roomDTO.getPosition());
            _ptmt.setInt(10, _roomDTO.getStatus());

            _ptmt.executeUpdate();
            _ptmt.close();
            return _roomDTO;
        } catch (SQLException _ex) {
            logger.log(Level.SEVERE, null, _ex);
            return null;
        } finally {
            DatabaseUtil.closeConnection(_con);
        }
    }

    /**
     * Read Room By Id
     * @param _roomId
     * @return RoomDTO
     */
    public RoomDTO readByID(String _roomId) {
        Connection _con = null;
        PreparedStatement _ptmt = null;
        RoomDTO _roomDTO = null;
        try {

            _con = DatabaseUtil.getConnection();
            _ptmt = _con.prepareStatement(SQL_READ_BY_ID);
            _ptmt.setString(1, _roomId);

            ResultSet _rs = _ptmt.executeQuery();
            if (_rs.next()) {
                _roomDTO = new RoomDTO();
                _roomDTO.setId(_rs.getString(1));
                _roomDTO.setName(_rs.getString(2));
                _roomDTO.setUser(new UserDAO().readById(_rs.getString(3)));
                _roomDTO.setContent(_rs.getString(4));
                _roomDTO.setImage(_rs.getString(5));
                _roomDTO.setPrice(_rs.getString(6));
                _roomDTO.setAlias(_rs.getString(7));
                _roomDTO.setWhen(_rs.getString(8));
                _roomDTO.setPosition(_rs.getInt(9));
                _roomDTO.setStatus(_rs.getInt(10));

            }
            _rs.close();
            _ptmt.close();
            return _roomDTO;
        } catch (SQLException _ex) {
            logger.log(Level.SEVERE, null, _ex);
            return null;
        } finally {
            DatabaseUtil.closeConnection(_con);
        }
    }

    /**
     * Read All Room
     * @return Collection<RoomDTO>
     */
    public Collection<RoomDTO> readByAll() {
        Collection<RoomDTO> _list = null;
        Connection _con = null;
        Statement _stmt = null;
        RoomDTO _roomDTO = null;
        try {
            _con = DatabaseUtil.getConnection();
            _stmt = _con.createStatement();
            ResultSet _rs = _stmt.executeQuery(SQL_READ_BY_ALL);

            _list = new ArrayList<RoomDTO>();
            while (_rs.next()) {
                _roomDTO = new RoomDTO();
                _roomDTO.setId(_rs.getString(1));
                _roomDTO.setName(_rs.getString(2));
                _roomDTO.setUser(new UserDAO().readById(_rs.getString(3)));
                _roomDTO.setContent(_rs.getString(4));
                _roomDTO.setImage(_rs.getString(5));
                _roomDTO.setPrice(_rs.getString(6));
                _roomDTO.setAlias(_rs.getString(7));
                _roomDTO.setWhen(_rs.getString(8));
                _roomDTO.setPosition(_rs.getInt(9));
                _roomDTO.setStatus(_rs.getInt(10));

                _list.add(_roomDTO);
            }
            _rs.close();
            _stmt.close();
            return _list;
        } catch (SQLException _ex) {
            logger.log(Level.SEVERE, null, _ex);
            return null;
        } finally {
            DatabaseUtil.closeConnection(_con);
        }
    }

    /**
     * Read Room by Status
     * @param _status
     * @return Collection<RoomDTO>
     */
    public Collection<RoomDTO> readByStatus(int _status) {
        Collection<RoomDTO> _list = null;
        Connection _con = null;
        PreparedStatement _ptmt = null;
        RoomDTO _roomDTO = null;
        try {
            _con = DatabaseUtil.getConnection();
            _ptmt = _con.prepareStatement(SQL_READ_BY_STATUS);
            _ptmt.setInt(1, _status);

            ResultSet _rs = _ptmt.executeQuery();
            _list = new ArrayList<RoomDTO>();
            while (_rs.next()) {
                _roomDTO = new RoomDTO();
                _roomDTO.setId(_rs.getString(1));
                _roomDTO.setName(_rs.getString(2));
                _roomDTO.setUser(new UserDAO().readById(_rs.getString(3)));
                _roomDTO.setContent(_rs.getString(4));
                _roomDTO.setImage(_rs.getString(5));
                _roomDTO.setPrice(_rs.getString(6));
                _roomDTO.setAlias(_rs.getString(7));
                _roomDTO.setWhen(_rs.getString(8));
                _roomDTO.setPosition(_rs.getInt(9));
                _roomDTO.setStatus(_rs.getInt(10));

                _list.add(_roomDTO);
            }
            _rs.close();
            _ptmt.close();
            return _list;
        } catch (SQLException _ex) {
            logger.log(Level.SEVERE, null, _ex);
            return null;
        } finally {
            DatabaseUtil.closeConnection(_con);
        }
    }

    /**
     * Read Room by Alias
     * @param _alias
     * @return Collection<RoomDTO>
     */
    public Collection<RoomDTO> readByAlias(String _alias) {
        Collection<RoomDTO> _list = null;
        Connection _con = null;
        PreparedStatement _ptmt = null;
        RoomDTO _roomDTO = null;
        try {
            _con = DatabaseUtil.getConnection();
            _ptmt = _con.prepareStatement(SQL_READ_BY_ALIAS);
            _ptmt.setString(1, _alias);

            ResultSet _rs = _ptmt.executeQuery();
            _list = new ArrayList<RoomDTO>();
            while (_rs.next()) {
                _roomDTO = new RoomDTO();
                _roomDTO.setId(_rs.getString(1));
                _roomDTO.setName(_rs.getString(2));
                _roomDTO.setUser(new UserDAO().readById(_rs.getString(3)));
                _roomDTO.setContent(_rs.getString(4));
                _roomDTO.setImage(_rs.getString(5));
                _roomDTO.setPrice(_rs.getString(6));
                _roomDTO.setAlias(_rs.getString(7));
                _roomDTO.setWhen(_rs.getString(8));
                _roomDTO.setPosition(_rs.getInt(9));
                _roomDTO.setStatus(_rs.getInt(10));

                _list.add(_roomDTO);
            }
            _rs.close();
            _ptmt.close();
            return _list;
        } catch (SQLException _ex) {
            
            logger.log(Level.SEVERE, null, _ex);
            return null;
        } finally {
            DatabaseUtil.closeConnection(_con);
        }
    }

    /**
     * Update Room
     * @param _roomDTO
     * @return RoomDTO
     */
    public RoomDTO update(RoomDTO _roomDTO) {
        Connection _con = null;
        PreparedStatement _ptmt = null;
        try {
            _con = DatabaseUtil.getConnection();
            _ptmt = _con.prepareStatement(SQL_UPDATE);

            _ptmt.setString(1, _roomDTO.getName());
            _ptmt.setString(2, _roomDTO.getUser().getUsername());
            _ptmt.setString(3, _roomDTO.getContent());
            _ptmt.setString(4, _roomDTO.getImage());
            _ptmt.setString(5, _roomDTO.getPrice());
            _ptmt.setString(6, _roomDTO.getAlias());
            _ptmt.setString(7, _roomDTO.getWhen());
            _ptmt.setInt(8, _roomDTO.getPosition());
            _ptmt.setInt(9, _roomDTO.getStatus());
            _ptmt.setString(10, _roomDTO.getId());

            _ptmt.executeUpdate();
            _ptmt.close();
            return _roomDTO;
        } catch (SQLException _ex) {
            logger.log(Level.SEVERE, null, _ex);
            return null;
        } finally {
            DatabaseUtil.closeConnection(_con);
        }
    }

    /**
     * Update Status
     * @param _roomId
     * @param _status
     * @return boolean
     */
    public boolean updateStatus(String _roomId, int _status) {
        Connection _con = null;
        PreparedStatement _ptmt = null;
        try {
            _con = DatabaseUtil.getConnection();
            _ptmt = _con.prepareStatement(SQL_UPDATE_STATUS);
            _ptmt.setInt(1, _status);
            _ptmt.setString(2, _roomId);

            _ptmt.executeUpdate();
            _ptmt.close();
            return true;
        } catch (SQLException _ex) {
            logger.log(Level.SEVERE, null, _ex);
            return false;
        } finally {
            DatabaseUtil.closeConnection(_con);
        }
    }

    /**
     * Delete Room
     * @param _roomId
     * @return boolean
     */
    public boolean deleteById(String _roomId) {
        Connection _con = null;
        PreparedStatement _ptmt = null;
        try {
            _con = DatabaseUtil.getConnection();
            _ptmt = _con.prepareStatement(SQL_DELETE_BY_ID);
            _ptmt.setString(1, _roomId);

            _ptmt.executeUpdate();
            _ptmt.close();
            return true;
        } catch (SQLException _ex) {
            logger.log(Level.SEVERE, null, _ex);
            return false;
        } finally {
            DatabaseUtil.closeConnection(_con);
        }
    }
}
